package cn.xiuxian.util;

import cn.xiuxian.util.temp.IPreResultCallBack;
import cn.xiuxian.util.temp.IPreparedCallBack;
import org.omg.CORBA.PUBLIC_MEMBER;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@SuppressWarnings("all")
public class JdbcTemplate {
    private  final  static    String    TABLE_PRE ="jdbc_";
    //DML
    public  static  int executeUpdate(IPreparedCallBack pcb){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int count = 0;
        try {
            connection = JdbcUtil.getConnection();

            preparedStatement = pcb.execute(connection);

            count = preparedStatement.executeUpdate();

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtil.closeConnection(connection,preparedStatement);
        }

        return  count;
    }

    //DQL
    public  static  Object  executeQuery(IPreparedCallBack pcb, IPreResultCallBack result){

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Object obj = null;
        try {

            connection = JdbcUtil.getConnection();
            preparedStatement = pcb.execute(connection);
            resultSet = preparedStatement.executeQuery();
            obj = result.execute(resultSet);

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtil.closeConnection(connection,preparedStatement,resultSet);
        }
        return  obj;
    }

    //关于反射封装.....  DML 操作
    public   static  int executeDML(String sql,Object... args){

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = JdbcUtil.getConnection();
            preparedStatement =connection.prepareStatement(sql);

            if (null != args && args.length >0) {
                for (int i = 0; i < args.length; i++) {
                    preparedStatement.setObject(i + 1, args[i]);
                }
            }

            return  preparedStatement.executeUpdate();

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtil.closeConnection(connection,preparedStatement);
        }
        return  0;
    }

    public  static   <T> T    queryOne(Class<?> c,String sql,Object... args){
        T t = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            if (null != args && args.length > 0){
                for (int i = 0; i < args.length; i++) {
                    preparedStatement.setObject(i+1,args[i]);
                }
            }
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData rsmd = resultSet.getMetaData();
            int count = rsmd.getColumnCount();
            if(resultSet.next()){
                t = (T) c.newInstance();

                for (int i = 1; i <= count; i++) {
                    String colname = rsmd.getColumnName(i);
                    if(colname.contains("_")){
                        colname = ColumnUtil.snake_Method(colname);
                    }
                    Field field = c.getDeclaredField(colname);
                    field.setAccessible(true);

                    field.set(t,resultSet.getObject(i));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeConnection(connection,preparedStatement,resultSet);
        }

        return  t;
    }

    public  static   <T> List<T> queryList(Class<?> c, String sql, Object... args){
        T t = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<T> list = new ArrayList<>();
        try {
            connection = PoolUtils.getConnection();
            System.out.println(connection.getClass());
            preparedStatement = connection.prepareStatement(sql);

            if (null != args && args.length > 0){
                for (int i = 0; i < args.length; i++) {
                    preparedStatement.setObject(i+1,args[i]);
                }
            }
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData rsmd = resultSet.getMetaData();
            int count = rsmd.getColumnCount();
           while (resultSet.next()){
                t = (T) c.newInstance();

                for (int i = 1; i <= count; i++) {
                    String colname = rsmd.getColumnName(i);
                    if(colname.contains("_")){
                        colname = ColumnUtil.snake_Method(colname);
                    }
                    Field field = c.getDeclaredField(colname);
                    field.setAccessible(true);

                    field.set(t,resultSet.getObject(i));
                }
                list.add(t);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeConnection(connection,preparedStatement,resultSet);
        }

        return  list;
    }

    public  static  <T> int   save(T t)  {
        Class<?> c = t.getClass();
        String name = c.getSimpleName();
        
        String  tablename = TABLE_PRE+name.toLowerCase();
        
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("insert into "+tablename + " (id,username,password,birthday,power) values ( ");
        Field[] fields = c.getDeclaredFields();
        if (null != fields && fields.length > 0){
            for (int i = 0; i < fields.length; i++) {
                if(i!=fields.length -1){
                    stringBuilder.append(" ?, ");
                }else{
                    stringBuilder.append(" ? ");
                }
            }
        }

        stringBuilder.append(" )");

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int count = 0;
        try {
            connection = JdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(stringBuilder.toString());
            if (null != fields && fields.length > 0){
                for (int i = 0; i < fields.length; i++) {
                    fields[i].setAccessible(true);
                    preparedStatement.setObject(i+1,fields[i].get(t));
                }
            }
            count = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.closeConnection(connection,preparedStatement);
        }
        return  count;
    }

    //针对getByid
    public  static  <T> T  getByid(Class<?> c , Serializable id){

        T t = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtil.getConnection();
            String  sql = "select  *  from  "+TABLE_PRE+c.getSimpleName()+" where id = ?";
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setObject(1,id);
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData rsmd = resultSet.getMetaData();
            int count = rsmd.getColumnCount();
            if(resultSet.next()){
                t = (T) c.newInstance();

                for (int i = 1; i <= count; i++) {
                    String colname = rsmd.getColumnName(i);
                    if(colname.contains("_")){
                        colname = ColumnUtil.snake_Method(colname);
                    }
                    Field field = c.getDeclaredField(colname);
                    field.setAccessible(true);

                    field.set(t,resultSet.getObject(i));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeConnection(connection,preparedStatement,resultSet);
        }

        return  t;
    }

    //针对getAll
    public  static <T> List<T> getAll(Class<?> c){

        T t = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<T> list = new ArrayList<>();
        try {
            connection = PoolUtils.getConnection();
            System.out.println(connection);
            System.out.println(connection.getClass());
            String sql = "select  *  from " +TABLE_PRE+c.getSimpleName();
            preparedStatement = connection.prepareStatement(sql);

            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData rsmd = resultSet.getMetaData();
            int count = rsmd.getColumnCount();
            while (resultSet.next()){
                t = (T) c.newInstance();

                for (int i = 1; i <= count; i++) {
                    String colname = rsmd.getColumnName(i);
                    if(colname.contains("_")){
                        colname = ColumnUtil.snake_Method(colname);
                    }
                    Field field = c.getDeclaredField(colname);
                    field.setAccessible(true);

                    field.set(t,resultSet.getObject(i));
                }
                list.add(t);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeConnection(connection,preparedStatement,resultSet);
        }

        return  list;
    }

    //针对delByid
    public  static   int  deleteByid(Class<?> c, Serializable id){

        return  executeUpdate(connection -> {
            String sql = "delete  from "+TABLE_PRE+c.getSimpleName()+" where  id = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1,id);

            return  preparedStatement;
        });
    }
}
